package study.top.mydb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import study.top.common.DBHandler;
import study.top.common.DateTime;
import study.top.jsp.PageHandler;
import study.top.mydb.items.ReplyItem;

public class Reply {
	
	private static final String TABLE = "reply";
	static Logger logger = Logger.getLogger(Reply.class);
	DBHandler db = null;

	public Reply() {
		// DB 객체에 대한 참조 연결
		db = DBHandler.getInstance();
	}

	public int write(String content,
			int board_seq, int member_seq) {
		
		
		int seq = -1;
		int rep_seq = db.getMax(TABLE, "seq", null) + 1;

		String datetime = DateTime.getInstance().getNowDateTimeString();

		String mainSql = "insert into reply (";
		mainSql += "seq, content, member_seq, board_seq, reg_date, edit_date";
		mainSql += ") values (";
		mainSql += "'" + rep_seq + "',";
		mainSql += "'" + content.replace("'", "\'") + "',";
		mainSql += "'" + member_seq + "',";
		mainSql += "'" + board_seq + "',";
		mainSql += "'" + datetime + "',";
		mainSql += "'" + datetime + "')";

		// 여러개의 SQL문이 실행되어야 하므로 트랜잭션 시작
		db.beginTrans();

		// 쿼리 수행
		boolean insertOk = db.execSql(mainSql);
		seq = rep_seq;

		if (!insertOk) {
			logger.error("Q&A 덧글 저장 실패");
			db.rollback();
			return seq; // -1
		}

		// insert에 대한 실제 데이터 저장
		db.commit();
		
		logger.debug("title: " + seq);
		logger.debug("====== method end >> replywrite");
		return seq;

	}
	
	public boolean delete(int seq, int member_seq) {
		boolean result = false;
								
		
		db.beginTrans();
		
		
		
		String sql2 = "DELETE FROM reply WHERE seq=%d and member_seq=%d";
		boolean result2 = db.execSql(sql2, seq, member_seq);
		
		if (!result2) {
			db.rollback();
			return result;
		}
		
		// 삭제 쿼리 결과 성공
		result = true;
		db.commit();
			
		return result;
	}
	
	public ArrayList<ReplyItem> list(int page, int board_seq) {
		ArrayList<ReplyItem> list = null;	
		
		// 리스트에 적용
		String count_table = "(SELECT COUNT(r.seq) ";
		count_table += "FROM reply r INNER JOIN board b ON b.seq = r.board_seq ";
		//count_table += "WHERE a.subject LIKE '%글% ';
		count_table += "where b.seq="+board_seq;
		count_table += " GROUP BY r.seq) ";
		count_table += " AS tbl";
		
		//  전체 글 갯수 조회
		int count = db.getCount(count_table, "*", null);
		
		if (count < 1) {
			return null;
		}
		
		// 페이징 처리 --> 한 페이지에 8개씩, 페이지번호는 5개 출력
		PageHandler p = PageHandler.getInstance(page, count, 8, 5);
		
		// 글 목록 조회
		String sql = "SELECT r.seq, r.content, r.board_seq, r.member_seq, m.id, r.reg_date, r.edit_date ";
		
		sql += "FROM reply r ";
		sql += "inner join member m on r.member_seq = m.seq ";
		//sql += "WHERE a.subject LIKE '%글% ';
		sql += "where r.board_seq=%d ";
		sql += "GROUP BY r.seq ";
		sql += "ORDER BY r.seq DESC ";
		sql += "LIMIT %d, %d";
		
		ResultSet rs = db.getResult(sql, board_seq, p.getStartRec(), p.getPageArticleCount());
		
		if (rs == null) {
			return null;
		}
		
		list = new ArrayList<ReplyItem>();
		
		try {
			while (rs.next()) {
				int seq = rs.getInt(1);
				//String title = rs.getString(2);
				String content = rs.getString(2);
				//int hit = rs.getInt(4);
				//String imgurl = rs.getString(5);
			//	int board_seq = rs.getInt(3);
				int member_seq = rs.getInt(4);
				String memberID = rs.getString(5);
				String reg_date = rs.getString(6);
				String edit_date = rs.getString(7);
				//String fileOrgName = rs.getString(10);
				//String fileSaveName = rs.getString(11);
				//String fileDir = rs.getString(12);
				//int fileSize = rs.getInt(13);
				//String fileType = rs.getString(14);
				
				
				
				ReplyItem item = new ReplyItem(seq, content, reg_date, edit_date, board_seq, member_seq, memberID);
				list.add(item);			
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
		return list;
	}

}
